package com.example.j280_webproject.userConsole.jdbc;

import com.example.j280_webproject.userConsole.pojo.User;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserJDBC{
    /**
     * 判断输入的用户名是否存在，如果存在返回一个count（id）为 1，不存在则返回为 0
     *
     * @param connection      数据库连接池
     * @param username        注册输入的用户名
     * @return                返回count(id)值
     * @throws SQLException   SQL异常
     */
    public int checkUsername(Connection connection, String username) throws SQLException {
        String sql = "select count(id) from user where username=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }

    /**
     * 注册用户信息，将输入注册的用户名和密码存入数据库用户表中
     *
     * @param connection      数据库连接池
     * @param username        注册输入的用户名
     * @param password        注册输入的用户密码
     * @throws SQLException   SQL异常
     */
    public void registerUser(Connection connection, String username, String password) throws SQLException {
        String sql ="insert into user(username, password) values (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    /**
     *登录：通过用户输入的用户名查询用户信息表对应的信息并封装成user对象返回，service比对密码是否相同判断登录是否成功
     *
     * @param connection      数据库连接池
     * @param username        登录输入的用户名
     * @return                返回User类型对象
     * @throws SQLException   SQL异常
     */
    public User loginUser(Connection connection, String username) throws SQLException {
        String sql = "select id, username, password from user where username=? limit 1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        if (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(username);
            user.setPassword(resultSet.getString("password"));
        }
        resultSet.close();
        preparedStatement.close();
        return user;
    }

    /**
     * 新增用户信息，将用户的用户名、密码、年龄添加到用户信息表
     *
     * @param connection      数据库连接池
     * @param username        新增输入的用户名
     * @param password        新增输入的用户密码
     * @param age             新增输入的用户年龄
     * @throws SQLException   SQL异常
     */
    public void insertUser(Connection connection, String username,String password,String age) throws SQLException {
        String sql = "insert into user(username, password, age) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        preparedStatement.setString(3,age);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    /**
     * 查询所有用户信息，将每一个用户的所有信息封装成json数据，存贮到list集合中返回
     *
     * @param connection      数据库连接池
     * @return                返回一个ArrayList的集合数组
     * @throws SQLException   SQL异常
     */
    public ArrayList<JSONObject> userList (Connection connection) throws SQLException {
        String sql = "select id, username, password, age from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        User user = null;
        while (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }

    /**
     * 通过用户的主键查询用户信息并返回一个user对象，将查询到的用户信息填充到需要修改的文本框中
     *
     * @param connection      数据库连接池
     * @param id              用户主键
     * @return                返回一个User类型的对象
     * @throws SQLException   SQL异常
     */
    public User selectUserById(Connection connection ,String id) throws SQLException {
        String sql = "select id, username, password, age from user where id=? limit 1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        if (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
        }
        resultSet.close();
        preparedStatement.close();
        return user;
    }

    /**
     * 编辑用户信息，将更新的用户信息在sql数据表中更新
     *
     * @param connection      数据库连接池
     * @param id              需要修改用户主键
     * @param username        修改用户的用户名
     * @param password        修改用户名的密码
     * @param age             修改用户的年龄
     * @throws SQLException
     */
    public void updateUserById(Connection connection,String id,String username,String password,String age) throws SQLException {
        String sql = "update user set username=?,password=?,age=? where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.setString(3, age);
        preparedStatement.setString(4, id);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    /**
     * 通过用户的主键删除此用户信息
     *
     * @param connection      数据库连接池
     * @param id              需要删除的用户主键
     * @throws SQLException   SQL异常
     */
    public void deleteUserById(Connection connection,String id) throws SQLException {
        String sql = "delete from user where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,id);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    /**
     * 统计user用户信息表中用户个数
     *
     * @param connection      数据库连接池
     * @return                返回一个int值
     * @throws SQLException   SQL异常
     */
    public int selectCount(Connection connection) throws SQLException {
        String sql = "select count(id) from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }

    /**
     * 翻页每页显示的用户数据，通过传入的startIndex，showCount值将每页用户信息封装传递给前端显示出来
     *
     * @param connection      数据库连接池
     * @param startIndex      每页显示的起始第几条用户信息
     * @param showCount       每页需要显示的用户条数
     * @return                返回一个封装了JSON数据的ArrayList集合
     * @throws SQLException   SQL数据库异常
     */
    public ArrayList<JSONObject> Page (Connection connection,int startIndex,int showCount) throws SQLException {
        String sql = "select id, username, password, age from user limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,startIndex);
        preparedStatement.setInt(2,showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        User user = null;
        while (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }

    /**
     * 模糊查询查询到的用户信息条数并返回一个int值
     *
     * @param connection      数据库连接池
     * @param username        模糊查询用户输入的用户名信息
     * @param password        模糊查询用户输入的用户密码
     * @return                返回一个int值，查询到的用户信息数
     * @throws SQLException   SQL数据库异常
     */
    public int selectLikeCount(Connection connection,String username,String password) throws SQLException {
        String sql = "select count(id) from user where username like ? and password like ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        preparedStatement.setString(2,"%"+password+"%");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }

    /**
     * 模糊查询翻页每页显示的用户数据，通过传入的startIndex，showCount值将每页用户信息封装传递给前端显示出来
     *
     * @param connection      数据库连接池
     * @param username        模糊查询用户输入的用户名信息
     * @param password        模糊查询用户输入的用户密码
     * @param startIndex      每页显示的起始第几条用户信息
     * @param showCount       每页需要显示的用户条数
     * @return                返回一个封装了JSON数据的ArrayList集合
     * @throws SQLException   SQL数据库异常
     */
    public ArrayList<JSONObject> selectLikeUserList (Connection connection,String username,String password,int startIndex,int showCount) throws SQLException {
        String sql = "select id, username, password, age from user where username like ? and password like ? limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        preparedStatement.setString(2,"%"+password+"%");
        preparedStatement.setInt(3,startIndex);
        preparedStatement.setInt(4,showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        User user = null;
        while (resultSet.next()){
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }
}
